﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using Newtonsoft.Json;

namespace Codeflows.Tracking.BuiltIn
{
    public class SqlServerTracker : ICodeflowTracker
    {
        const string Fields =
            "ci.Id, ci.CodeflowType, ci.FriendlyName, ci.Status, ci.TimeToWakeup, ci.LockedUntilUtc, ci.DispatcherId, " +
            "ci.ActionName, ci.DispatchAttemptsCount, ci.ArgumentsJson, ci.StartedAtUtc, " +
            "ci.SerializedArguments, ci.CreatedOnUtc, ci.ModifiedOnUtc ";

        readonly SqlConnection _connection;
        readonly bool _ownedConnection;

        public SqlServerTracker()
        {
            _connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Codeflows"].ConnectionString);
            _connection.Open();
            _ownedConnection = true;
        }

        public SqlServerTracker(DbConnection connection)
        {
            if (!(connection is SqlConnection))
                throw new ArgumentException("Only SqlConnection is allowed.");

            _connection = (SqlConnection) connection;

            if (_connection.State == ConnectionState.Closed)
                _connection.Open();
        }

        public void Dispose()
        {
            if (_connection != null && _ownedConnection)
                _connection.Dispose();
        }

        public Guid Create(CodeflowInstance codeflowInstance)
        {
            var currentAction = codeflowInstance.CurrentAction;
            if(currentAction == null)
                throw new InvalidOperationException("Unable to create a CodeflowInstance without an entrypoint.");
            
            using (var command = _connection.CreateCommand())
            {
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = "CreateCodeflowInstance";
                
                command.WithParameter("codeflowType", codeflowInstance.CodeflowType)
                    .WithParameter("friendlyName", codeflowInstance.FriendlyName)
                    .WithParameter("status", codeflowInstance.Status);

                if(codeflowInstance.TimeToWakeUp.HasValue)
                    command.WithParameter("timeToWakeUp", codeflowInstance.TimeToWakeUp);
                else
                    command.WithParameter("timeToWakeUp", DBNull.Value);

                command.WithParameter("lockedUntilUtc", codeflowInstance.LockedUntilUtc)
                    .WithParameter("dispatcherId", codeflowInstance.DispatcherId)
                    .WithParameter("createdOnUtc", codeflowInstance.CreatedOnUtc)
                    .WithParameter("modifiedOnUtc", codeflowInstance.ModifiedOnUtc);            

                var argumentsJson = JsonConvert.SerializeObject(currentAction.Arguments);

                command.WithParameter("actionName", currentAction.Name)
                    .WithParameter("dispatchAttemptsCount", currentAction.DispatchAttemptsCount)
                    .WithParameter("argumentsJson", argumentsJson)
                    .WithParameter("startedAtUtc", currentAction.StartedAtUtc)
                    .WithParameter("serializedArguments", currentAction.Arguments.Pack());

                codeflowInstance.CodeflowId = (Guid) command.ExecuteScalar();
            }
            
            return codeflowInstance.CodeflowId;
        }

        void RecordCodeflowHistory(CodeflowInstance codeflowInstance, Guid actionId)
        {
            using (var command = _connection.CreateCommand())
            {
                command.CommandText =
                    "INSERT INTO [dbo].[CodeflowInstanceHistory] ([CodeflowInstanceId], [CodeflowActionId]) " +
                    "VALUES (@codeflowInstanceId, @codeflowActionId)";

                command.WithParameter("codeflowInstanceId", codeflowInstance.CodeflowId)
                    .WithParameter("codeflowActionId", actionId);

                command.ExecuteScalar();
            }
        }
        
        public CodeflowInstance Read(Guid codeflowId)
        {
            var query =
                string.Format(
                    "SELECT {0} FROM CodeflowInstances ci WITH (ROWLOCK UPDLOCK) " +                     
                    "WHERE ci.Id = @codeflowId",
                    Fields);

            using (var command = _connection.CreateCommand().WithQuery(query).WithParameter("codeflowId", codeflowId))
            using (var reader = command.ExecuteReader())
            {
                if (reader.Read())
                    return LoadFromReader(reader);

                throw DiagnosticsUtility.CreateException(
                    "Unable to find a codeflow instance with id {0} in persistance store.", codeflowId);
            }
        }

        public IList<CodeflowInstance> ReadNext(int count)
        {
            var instances = new List<CodeflowInstance>();
            using (var command = _connection.CreateCommand())
            {
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = "ReadNextBatch";
                command.WithParameter("batchSize", count);
                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var codeflowInstance = LoadFromReader(reader);
                        instances.Add(codeflowInstance);
                    }
                    reader.Close();
                }    
            }            

            return instances;
        }

        public IList<CodeflowInstance> ReadReadyList(int count)
        {
            var query =
                string.Format(
                    "SELECT TOP {0} {1} FROM CodeflowInstances ci WITH (ROWLOCK READPAST UPDLOCK) " +
                    "WHERE ((ci.Status = 4) AND ci.TimeToWakeUp < GETUTCDATE()) OR " +
                    "ci.Status IN ('1', '2') AND ci.LockedUntilUtc < GETUTCDATE()" +
                    "ORDER BY ci.Id ASC",
                    count,
                    Fields);

            var instances = new List<CodeflowInstance>();
            using (var command = _connection.CreateCommand().WithQuery(query))
            using (var reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    var codeflowInstance = LoadFromReader(reader);

                    instances.Add(codeflowInstance);
                }
                reader.Close();
            }

            return instances;
        }

        public void Update(IEnumerable<CodeflowInstance> codeflowInstances)
        {
            foreach (var codeflowInstance in codeflowInstances)
            {
                UpdateCore(codeflowInstance);
            }
        }

        public void Update(CodeflowInstance codeflowInstance)
        {
            Update(new[] {codeflowInstance});
        }

        void UpdateCore(CodeflowInstance codeflowInstance)
        {
            if (codeflowInstance.LastAction != null)
                CreateHistoryRecordForAction(codeflowInstance.CodeflowId, codeflowInstance.LastAction);                        

            using(var command = _connection.CreateCommand())
            {
                command.WithQuery("UPDATE [CodeflowInstances] SET [Status] = @status, " +
                                  "[TimeToWakeUp] = @timeToWakeUp, [LockedUntilUtc] = @lockedUntilUtc, " +
                                  "[DispatcherId] = @dispatcherId, [ActionName] = @actionName, " +
                                  "[ArgumentsJson] = @argumentsJson, [DispatchAttemptsCount] = @dispatchAttemptsCount, " +
                                  "[StartedAtUtc] = @startedAtUtc, [SerializedArguments] = @serializedArguments, " +
                                  "[ModifiedOnUtc] = @modifiedOnUtc " +
                                  "WHERE Id = @id;")
                    .WithParameter("id", codeflowInstance.CodeflowId)
                    .WithParameter("status", (int) codeflowInstance.Status)
                    .WithParameter("lockedUntilUtc", codeflowInstance.LockedUntilUtc)
                    .WithParameter("dispatcherId", codeflowInstance.DispatcherId)
                    .WithParameter("modifiedOnUtc", codeflowInstance.ModifiedOnUtc);                                                           
                
                if (codeflowInstance.TimeToWakeUp == null)
                    command.WithParameter("timeToWakeUp", DBNull.Value);
                else
                    command.WithParameter("timeToWakeUp", codeflowInstance.TimeToWakeUp);

                var currentAction = codeflowInstance.CurrentAction ?? codeflowInstance.LastAction;

                command.WithParameter("actionName", currentAction.Name)
                    .WithParameter("argumentsJson", JsonConvert.SerializeObject(currentAction.Arguments))
                    .WithParameter("dispatchAttemptsCount", currentAction.DispatchAttemptsCount)
                    .WithParameter("startedAtUtc", currentAction.StartedAtUtc)
                    .WithParameter("serializedArguments", currentAction.Arguments.Pack());
                       
                command.ExecuteScalar();
            }            
        }

        void CreateHistoryRecordForAction(Guid codeflowId, CodeflowAction action)
        {
            using (var command = _connection.CreateCommand())
            {
                command.WithQuery(
                    "INSERT INTO [dbo].[CodeflowActions] ([CodeflowId], [ActionName], [ArgumentsJson], [DispatchAttemptsCount], " +
                    "[StartedAtUtc], [CompletedAtUtc], [Result], [ExceptionData])" +
                    "VALUES (@codeflowId, @actionName, @argumentsJson, @dispatchAttemptsCount, @startedAtUtc, @completedAtUtc, @result, @exceptionData)")
                    .WithParameter("codeflowId", codeflowId)
                    .WithParameter("actionName", action.Name)
                    .WithParameter("argumentsJson", JsonConvert.SerializeObject(action.Arguments))
                    .WithParameter("dispatchAttemptsCount", action.DispatchAttemptsCount)
                    .WithParameter("startedAtUtc", action.StartedAtUtc)
                    .WithParameter("completedAtUtc", action.CompletedAtUtc)
                    .WithParameter("result", (int) action.Result);                    

                if (action.ExceptionData == null)
                    command.WithParameter("exceptionData", DBNull.Value);
                else
                    command.WithParameter("exceptionData", action.ExceptionData);

                command.ExecuteScalar();
            }
        }


        static CodeflowInstance LoadFromReader(SqlDataReader reader)
        {
            var row = new DataRow(reader);
            return new CodeflowInstance(row.CodeflowId, row.CodeflowType, row.FriendlyName,
                                        (CodeflowStatus)row.Status,
                                        row.TimeToWakup, row.CreatedOnUtc, row.ModifiedOnUtc,
                                        row.LockedUntilUtc, row.DispatcherId,
                                        new CodeflowAction(row.ActionName, row.SerializedArguments.Unpack(),
                                                                   row.DispatchAttemptsCount, row.StartedAtUtc));
        }
    }
}